package net.hn.hnms.biz.quality.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.hn.hnms.biz.quality.domain.CoalQualityPlan;
import net.hn.hnms.biz.quality.vo.CoalQualityDataVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CoalQualityPlanMapper extends BaseMapper<CoalQualityPlan> {



    @Select("<script>" +
            " SELECT\n" +
            "    TO_CHAR(date_trunc('month', COALESCE(plan.start_date, actual.coal_quality_date)), 'YYYY-MM') AS month,\n" +
            "    COALESCE(MAX(CASE WHEN plan.data_type = 'plan' THEN plan.ash END), 0) AS plan_ash,\n" +
            "    COALESCE(MAX(CASE WHEN actual.data_type = 'actual' THEN actual.ash END), 0) AS actual_ash,\n" +
            "    COALESCE(MAX(CASE WHEN plan.data_type = 'plan' THEN plan.moisture_total END), 0) AS plan_moisture_total,\n" +
            "    COALESCE(MAX(CASE WHEN actual.data_type = 'actual' THEN actual.moisture_total END), 0) AS actual_moisture_total,\n" +
            "    COALESCE(MAX(CASE WHEN plan.data_type = 'plan' THEN plan.shale_content END), 0) AS plan_shale_content,\n" +
            "    COALESCE(MAX(CASE WHEN actual.data_type = 'actual' THEN actual.shale_content END), 0) AS actual_shale_content,\n" +
            "    COALESCE(MAX(CASE WHEN plan.data_type = 'plan' THEN plan.net_calorific_value END), 0) AS plan_net_calorific_value,\n" +
            "    COALESCE(MAX(CASE WHEN actual.data_type = 'actual' THEN actual.net_calorific_value END), 0) AS actual_net_calorific_value\n" +
            "  FROM\n" +
            "    (SELECT\n" +
            "        start_date,\n" +
            "        ash,\n" +
            "        moisture_total,\n" +
            "        shale_content,\n" +
            "        net_calorific_value,\n" +
            "        plan_code,\n" +
            "        'plan' AS data_type\n" +
            "    FROM\n" +
            "        coal_quality_plan\n" +
            "    WHERE\n" +
            "        start_date <![CDATA[ >= ]]> DATE_TRUNC('month', NOW() - INTERVAL '12 months') \n" +
            "        AND end_date <![CDATA[ <= ]]> NOW() \n" +
            "        AND del_flag = '0' \n" +
            "            <if test='mineCode!= null and mineCode != \"\"'>" +
            "         AND mine_code = #{mineCode} \n" +
            "            </if>" +

            "    ) AS plan\n" +
            "FULL OUTER JOIN\n" +
            "    (SELECT\n" +
            "        coal_quality_date,\n" +
            "        ash,\n" +
            "        moisture_total,\n" +
            "        shale_content,\n" +
            "        net_calorific_value,\n" +
            "        sales_volume,\n" +
            "        volatile_matter,\n" +
            "        'actual' AS data_type\n" +
            "    FROM\n" +
            "        coal_quality\n" +
            "    WHERE\n" +
            "        coal_quality_date <![CDATA[ >= ]]> DATE_TRUNC('month', NOW() - INTERVAL '12 months') \n" +
            "\t\t\t\t AND coal_quality_date <![CDATA[ <= ]]> NOW()\n" +
            "        AND del_flag = '0' \n" +
            "            <if test='mineCode!= null and mineCode != \"\"'>" +
            "         AND mine_code = #{mineCode} \n" +
            "            </if>" +
            "    ) AS actual ON plan.ash = actual.ash AND plan.moisture_total = actual.moisture_total\n" +
            "        AND plan.shale_content = actual.shale_content AND plan.net_calorific_value = actual.net_calorific_value\n" +
            "GROUP BY\n" +
            "    month\n" +
            "ORDER BY\n" +
            "    month;\n " +
            "</script>")
    List<CoalQualityDataVo> selectListByDate(@Param("mineCode") String mineCode);
}









